﻿/*
   Copyright 2013 Dmitry Bratus

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using Flower.Directory.Host;
using Flower.Directory.Scripting;
using Flower.Logging;
using Flower.Security;
using Flower.Services;
using Flower.Services.Data;
using System.Threading.Tasks;
using System.ServiceModel;
using System.Transactions;
using System.Security.Principal;
using IsolationLevel = System.Transactions.IsolationLevel;
using SortOrder = Flower.Services.Data.SortOrder;
using System.Collections.Concurrent;

namespace Flower.SqlServer.Directory
{
    public class Directory : IDirectory, IProcessesDispatcherHolder
    {
        private static readonly Log Log = LogManager.CreateLog("SqlServer.Directory");

        private readonly IAuthorizationProvider _authorizationProvider;
        private readonly string _connectionString;
        private readonly IDirectory _selfProxy;
        private readonly ScriptRunner _scriptRunner;

        private readonly IProcessesDispatcher _processesDispatcher;
        
        public IProcessesDispatcher ProcessesDispatcher
        {
            get { return _processesDispatcher; }
        }

        #region Current user

        private static IIdentity GetCurrentUser()
        {
            return 
                (ServiceSecurityContext.Current != null) ? 
                    ServiceSecurityContext.Current.WindowsIdentity : 
                    WindowsIdentity.GetAnonymous();
        }

        #endregion

        #region Constructor

        public Directory
        (
            Settings settings,
            IAuthorizationProvider authorizationProvider,
            IScriptEnginePool scriptEnginePool,
            IDirectoryProxyProvider directoryProxyProvider = null,
            IProcessesDispatcher processesDispatcher = null
        )
        {
            _authorizationProvider = authorizationProvider;
            _selfProxy = (directoryProxyProvider != null) ? directoryProxyProvider.MakeProxy(this) : this;
            _scriptRunner = new ScriptRunner(scriptEnginePool, _selfProxy);
            _processesDispatcher = processesDispatcher;

            if (_processesDispatcher != null)
            {
                _processesDispatcher.Directory = this;
            }

            ConnectionStringSettings connStrSettings =
                ConfigurationManager.ConnectionStrings[settings.ConnectionStringName];

            if (connStrSettings == null)
            {
                throw new InvalidOperationException(string.Format("Connection string '{0}' not found.", settings.ConnectionStringName));
            }

            _connectionString = connStrSettings.ConnectionString;

            _authorizationProvider.Directory = _selfProxy;
        }

        #endregion
        
        #region Reading entries

        private class SpecificFieldsReader : IDirectoryEntryVisitor
        {
            public SqlDataReader Reader;

            public void Visit(Assembly assembly)
            {
                assembly.Image = (byte[])Reader["Image"];
            }

            public void Visit(Folder folder)
            {
            }

            public void Visit(Link link)
            {
                link.Target = (string)Reader["Target"];
            }

            public void Visit(Message message)
            {
                var data = Reader["Data"] as byte[];

                if (data != null)
                {
                    message.Data = new Blob
                    {
                        Data = data,
                        Format = (BlobFormat)(int)Reader["DataFormat"]
                    };
                }
            }

            public void Visit(Process process)
            {
                process.Workflow = (string)Reader["Workflow"];
            }

            public void Visit(Processor processor)
            {
                var configuration = Reader["Configuration"] as byte[];

                if (configuration != null)
                {
                    processor.Configuration = new Blob
                    {
                        Data = configuration,
                        Format = (BlobFormat)(int)Reader["ConfigurationFormat"]
                    };
                }
            }

            public void Visit(Role role)
            {
            }

            public void Visit(ServiceContainer serviceContainer)
            {
                var configuration = Reader["Configuration"] as byte[];

                if (configuration != null)
                {
                    serviceContainer.Configuration = new Blob
                    {
                        Data = configuration,
                        Format = (BlobFormat)(int)Reader["ConfigurationFormat"]
                    };
                }
            }

            public void Visit(Set set)
            {
                set.MessageType = (string)Reader["MessageType"];
                set.Capacity = (int)Reader["Capacity"];
            }

            public void Visit(State state)
            {
                state.Data = new Blob
                {
                    Data = (byte[])Reader["Data"],
                    Format = (BlobFormat)(int)Reader["DataFormat"]
                };
            }

            public void Visit(Workflow workflow)
            {
                workflow.DefinitionType = (string)Reader["DefinitionType"];
            }

            public void Visit(DirectoryEntryInfo directoryEntryInfo)
            {
            }
        }

        private static DirectoryEntry ReadCommonFields(DirectoryEntry directoryEntry, SqlDataReader reader)
        {
            directoryEntry.Id = "#" + reader["Id"];
            directoryEntry.Name = (string)reader["Name"];
            directoryEntry.CreatedOn = (DateTime)reader["CreatedOn"];
            directoryEntry.UpdatedOn = (DateTime)reader["UpdatedOn"];

            return directoryEntry;
        }

        private static DirectoryEntry ReadDirectoryEntry(SpecificFieldsReader specificFieldsReader, SqlDataReader reader)
        {
            var type = (DirectoryEntryTypes)(int)reader["Type"];
            
            DirectoryEntry directoryEntry;

            switch (type)
            {
                case DirectoryEntryTypes.Assembly:
                    {
                        directoryEntry = ReadCommonFields(new Assembly(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Folder:
                    {
                        directoryEntry = ReadCommonFields(new Folder(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Link:
                    {
                        directoryEntry = ReadCommonFields(new Link(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Message:
                    {
                        directoryEntry = ReadCommonFields(new Message(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Process:
                    {
                        directoryEntry = ReadCommonFields(new Process(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Processor:
                    {
                        directoryEntry = ReadCommonFields(new Processor(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Role:
                    {
                        directoryEntry = ReadCommonFields(new Role(), reader);
                    }
                    break;
                case DirectoryEntryTypes.ServiceContainer:
                    {
                        directoryEntry = ReadCommonFields(new ServiceContainer(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Set:
                    {
                        directoryEntry = ReadCommonFields(new Set(), reader);
                    }
                    break;
                case DirectoryEntryTypes.State:
                    {
                        directoryEntry = ReadCommonFields(new State(), reader);
                    }
                    break;
                case DirectoryEntryTypes.Workflow:
                    {
                        directoryEntry = ReadCommonFields(new Workflow(), reader);
                    }
                    break;
                default:
                    throw new ArgumentOutOfRangeException();
            }

            if (specificFieldsReader != null)
            {
                specificFieldsReader.Reader = reader;
                directoryEntry.AcceptVisitor(specificFieldsReader);
            }

            return directoryEntry;
        }

        private static DirectoryEntryInfo ReadDirectoryEntryInfo(SqlDataReader reader)
        {
            var info = (DirectoryEntryInfo)ReadCommonFields(new DirectoryEntryInfo(), reader);

            info.Type = (DirectoryEntryTypes)(int)reader["Type"];

            return info;
        }

        private static AccessControlEntry ReadAcl(SqlDataReader reader)
        {
            return new AccessControlEntry
            {
                Role = (string)reader["Role"],
                Permission = (Permission)(int)reader["Permission"]
            };
        }

        private static void ReadApplyAcl(IEnumerable<DirectoryEntry> directoryEntries, SqlDataReader reader)
        {
            var ace = new AccessControlEntry
            {
                Role = (string)reader["Role"],
                Permission = (Permission)(int)reader["Permission"]
            };

            string id = "#" + reader["DirectoryEntryId"];

            foreach (DirectoryEntry directoryEntry in directoryEntries)
            {
                if (directoryEntry.Id == id)
                {
                    directoryEntry.AccessControlList.Add(ace);
                    break;
                }
            }
        }

        private static void ReadApplyRoleUser(IEnumerable<DirectoryEntry> directoryEntries, SqlDataReader reader)
        {
            string id = "#" + reader["DirectoryEntryId"];

            foreach (DirectoryEntry directoryEntry in directoryEntries)
            {
                if (directoryEntry.Id == id)
                {
                    ((Role)directoryEntry).Users.Add((string)reader["UserName"]);
                    break;
                }
            }
        }

        private static void ReadApplyWorkflowPreferredProcessor(IEnumerable<DirectoryEntry> directoryEntries, SqlDataReader reader)
        {
            string id = "#" + reader["DirectoryEntryId"];

            foreach (DirectoryEntry directoryEntry in directoryEntries)
            {
                if (directoryEntry.Id == id)
                {
                    ((Workflow)directoryEntry).PreferredProcessors.Add((string)reader["ProcessorName"]);
                    break;
                }
            }
        }

        private static void ReadApplyData(SpecificFieldsReader specificFieldsReader, IEnumerable<DirectoryEntry> directoryEntries, SqlDataReader reader)
        {
            string id = "#" + reader["DirectoryEntryId"];

            foreach (DirectoryEntry directoryEntry in directoryEntries)
            {
                if (directoryEntry.Id == id)
                {
                    specificFieldsReader.Reader = reader;
                    directoryEntry.AcceptVisitor(specificFieldsReader);
                    break;
                }
            }
        }

        #endregion

        #region Saving entries

        private class SpecificFieldsSaver : IDirectoryEntryVisitor
        {
            public SqlCommandBuilder Command;

            public void Visit(Assembly assembly)
            {
                Command.AddParameter("@Image", assembly.Image);
            }

            public void Visit(Folder folder)
            {
            }

            public void Visit(Link link)
            {
                Command.AddParameter("@Target", link.Target);
            }

            public void Visit(Message message)
            {
                if (message.Data == null)
                {
                    Command
                        .AddParameterNull("@Data")
                        .AddParameterNull("@DataFormat");
                }
                else
                {
                    Command
                        .AddParameter("@Data", message.Data.Data)
                        .AddParameter("@DataFormat", (int)message.Data.Format);
                }
            }

            public void Visit(Process process)
            {
                Command.AddParameter("@Workflow", process.Workflow);
            }

            public void Visit(Processor processor)
            {
                if (processor.Configuration == null)
                {
                    Command
                        .AddParameterNull("@Configuration")
                        .AddParameterNull("@ConfigurationFormat");
                }
                else
                {
                    Command
                        .AddParameter("@Configuration", processor.Configuration.Data)
                        .AddParameter("@ConfigurationFormat", (int)processor.Configuration.Format);
                }
            }

            public void Visit(Role role)
            {
                Command.AddParameter("@Users", role.Users);
            }

            public void Visit(ServiceContainer serviceContainer)
            {
                Command
                    .AddParameter("@Configuration", serviceContainer.Configuration.Data)
                    .AddParameter("@ConfigurationFormat", (int)serviceContainer.Configuration.Format);
            }

            public void Visit(Set set)
            {
                Command
                    .AddParameter("@MessageType", set.MessageType)
                    .AddParameter("@Capacity", set.Capacity);
            }

            public void Visit(State state)
            {
                Command
                    .AddParameter("@Data", state.Data.Data)
                    .AddParameter("@DataFormat", (int)state.Data.Format);
            }

            public void Visit(Workflow workflow)
            {
                Command
                    .AddParameter("@DefinitionType", workflow.DefinitionType)
                    .AddParameter("@PreferredProcessors", workflow.PreferredProcessors);
            }

            public void Visit(DirectoryEntryInfo directoryEntryInfo)
            {
            }
        }

        private static void AddCommonParameters(SqlCommandBuilder command, DirectoryEntry directoryEntry)
        {
            command
                .AddParameter("@PathOrId", directoryEntry.Id)
                .AddParameter("@Name", directoryEntry.Name);
        }

        #endregion

        #region IDirectory implementation

        public string GetPathById(string id)
        {
            long idLong;

            try
            {
                idLong = Util.IdToLong(id);
            }
            catch (Exception)
            {
                throw new FaultException<DirectoryFault>
                (
                    new DirectoryFault
                    {
                        Code = DirectoryFaultCode.InvalidIdFormat
                    },
                    "Invalid id format"
                );
            }

            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var command = new SqlCommandBuilder(connection))
                {
                    string result =
                        command
                            .SetText("dbo.GetPathById")
                            .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                            .AddParameter("@Id", idLong)
                            .ExecuteStringFunction();

                    if (result == null)
                    {
                        throw new FaultException<DirectoryFault>
                        (
                            new DirectoryFault
                            {
                                Code = DirectoryFaultCode.EntryNotFound
                            },
                            "Entry with the specified id doesn't exist."
                        );
                    }

                    return result;
                }
            }
        }

        private static void LoadAcl(SqlConnection connection, DirectoryEntry directoryEntry)
        {
            long id = Util.IdToLong(directoryEntry.Id);

            using (var aclCommand = new SqlCommandBuilder(connection))
            {
                aclCommand
                    .SetText("dbo.GetAccessControlList")
                    .AddParameter("@Id", id);

                var acl =
                    aclCommand
                        .ExecuteReader(ReadAcl);

                foreach (AccessControlEntry ace in acl)
                {
                    directoryEntry.AccessControlList.Add(ace);
                }
            }
        }

        public DirectoryEntry Get(string what)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var getDirectoryEntryCommand = new SqlCommandBuilder(connection))
                {
                    getDirectoryEntryCommand
                        .SetText("dbo.GetDirectoryEntry")
                        .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                        .AddParameter("@PathOrId", what);

                    DirectoryEntry result;

                    try
                    {
                        result = getDirectoryEntryCommand.ExecuteSingleRow(rdr => ReadDirectoryEntry(new SpecificFieldsReader(), rdr));
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result == 1 || ex.Result == 2)
                        {
                            return null;
                        }

                        throw;
                    }

                    if (result == null)
                    {
                        return null;
                    }

                    Task additionalDataLoading = null;
                    var transaction = Transaction.Current;

                    if (result is Role)
                    {
                        var role = (Role)result;

                        additionalDataLoading = Task.Factory.StartNew(() =>
                        {
                            var prevTransaction = Transaction.Current;
                            Transaction.Current = transaction;

                            try
                            {
                                using (var roleUsersCommand = new SqlCommandBuilder(connection))
                                {
                                    roleUsersCommand
                                        .SetText("dbo.GetRoleUsers")
                                        .AddParameter("@Id", Util.IdToLong(result.Id));

                                    var userNames =
                                        roleUsersCommand
                                            .ExecuteReader(rdr => rdr.GetString(0));

                                    foreach (string userName in userNames)
                                    {
                                        role.Users.Add(userName);
                                    }
                                }
                            }
                            catch(Exception ex)
                            {
                                Log.Error("Failed to load users of a role.", ex);
                                throw;
                            }
                            finally
                            {
                                Transaction.Current = prevTransaction;
                            }
                        });
                    }
                    else if (result is Workflow)
                    {
                        var wf = (Workflow)result;

                        additionalDataLoading = Task.Factory.StartNew(() =>
                        {
                            var prevTransaction = Transaction.Current;
                            Transaction.Current = transaction;

                            try
                            {
                                using (var wfPreferredProcsCommand = new SqlCommandBuilder(connection))
                                {
                                    wfPreferredProcsCommand
                                        .SetText("dbo.GetWorkflowPreferredProcessors")
                                        .AddParameter("@Id", Util.IdToLong(result.Id));

                                    var preferredProcessors =
                                        wfPreferredProcsCommand
                                            .ExecuteReader(rdr => rdr.GetString(0));

                                    foreach (string proc in preferredProcessors)
                                    {
                                        wf.PreferredProcessors.Add(proc);
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                Log.Error("Failed to load preferred processors of a workflow.", ex);
                                throw;
                            }
                            finally
                            {
                                Transaction.Current = prevTransaction;
                            }
                        });
                    }

                    Exception aclLoadingException = null;

                    try
                    {
                        LoadAcl(connection, result);
                    }
                    catch(Exception ex)
                    {
                        aclLoadingException = ex;
                        Log.Error("ACL loading failed", ex);
                    }

                    if (additionalDataLoading != null)
                    {
                        additionalDataLoading.Wait();
                    }

                    if (aclLoadingException != null)
                    {
                        throw new FaultException<DirectoryFault>
                        (
                            new DirectoryFault 
                            {
                                Code = DirectoryFaultCode.ServerError
                            }
                        );
                    }

                    return result;
                }
            }
        }

        public DirectoryEntryInfo GetInfo(string what)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var getDirectoryEntryCommand = new SqlCommandBuilder(connection))
                {
                    getDirectoryEntryCommand
                        .SetText("dbo.GetInfo")
                        .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                        .AddParameter("@PathOrId", what);

                    DirectoryEntryInfo result;

                    try
                    {
                        result = getDirectoryEntryCommand.ExecuteSingleRow(ReadDirectoryEntryInfo);
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result == 1)
                        {
                            return null;
                        }

                        throw;
                    }

                    if (result == null)
                    {
                        return null;
                    }

                    LoadAcl(connection, result);

                    return result;
                }
            }
        }

        public IList<DirectoryEntry> GetChildren(string parent, DirectoryEntryTypes entryTypes, SortProperty property, SortOrder order, long offset, long limit)
        {
            var transaction = Transaction.Current;

            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                var children = new List<DirectoryEntry>();

                bool isSingleTypeFilter = Util.IsSingleBit((int)entryTypes);
                bool limitFilter = limit > offset;
                bool offsetFilter = offset > 0 || limitFilter;
                
                string procName =
                    string.Concat
                    (
                        "dbo.Get",
                        (isSingleTypeFilter) ? entryTypes.ToString() : string.Empty,
                        "Children",
                        (!isSingleTypeFilter) ? "Info" : string.Empty,
                        (offsetFilter) ? "Offset" : string.Empty,
                        (limitFilter) ? "Limit" : string.Empty,
                        property.ToString(),
                        order.ToString()
                    );

                var dataLoadingTasks = new List<Task>();

                using (var getDirectoryEntriesCommand = new SqlCommandBuilder(connection))
                {
                    getDirectoryEntriesCommand
                        .SetText(procName)
                        .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                        .AddParameter("@Parent", parent);

                    if (!isSingleTypeFilter)
                    {
                        getDirectoryEntriesCommand
                            .AddParameter("@Type", (int)entryTypes);
                    }

                    if (offsetFilter)
                    {
                        getDirectoryEntriesCommand
                            .AddParameter("@Offset", offset);
                    }

                    if (limitFilter)
                    {
                        getDirectoryEntriesCommand
                            .AddParameter("@Limit", limit);
                    }

                    var specificFieldsReader = (isSingleTypeFilter) ? new SpecificFieldsReader() : null;
                    var typeIdMap = new Dictionary<DirectoryEntryTypes, List<long>>();

                    try
                    {
                        children.AddRange
                        (
                            getDirectoryEntriesCommand.ExecuteReader
                            (
                                rdr => 
                                {
                                    var type = (DirectoryEntryTypes)(int)rdr["Type"];

                                    List<long> ids;
                                    if (!typeIdMap.TryGetValue(type, out ids))
                                    {
                                        ids = new List<long>();
                                        typeIdMap.Add(type, ids);
                                    }

                                    ids.Add((long)rdr["Id"]);
                                    
                                    return ReadDirectoryEntry(specificFieldsReader, rdr);
                                }
                            )
                        );
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result > 1)
                        {
                            throw;
                        }
                    }

                    if (children.Count == 0)
                    {
                        return children;
                    }
                    
                    if (!isSingleTypeFilter)
                    {
                        specificFieldsReader = new SpecificFieldsReader();

                        foreach (KeyValuePair<DirectoryEntryTypes, List<long>> typeId in typeIdMap)
                        {
                            string entryTypeName = typeId.Key.ToString();
                            IEnumerable<long> ids = typeId.Value;

                            if (typeId.Key != DirectoryEntryTypes.Folder && typeId.Key != DirectoryEntryTypes.Role)
                            {
                                dataLoadingTasks.Add
                                (
                                    Task.Factory.StartNew
                                    (
                                        () =>
                                        {
                                            var prevTransaction = Transaction.Current;
                                            Transaction.Current = transaction;

                                            try
                                            {
                                                using (var dataCommand = new SqlCommandBuilder(connection))
                                                {
                                                    dataCommand
                                                        .SetText(string.Concat("dbo.Get", entryTypeName, "Data"))
                                                        .AddParameter("@Ids", ids)
                                                        .ExecuteReaderWithAction
                                                        (
                                                            rdr =>
                                                                ReadApplyData(specificFieldsReader, children, rdr)
                                                        );
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                Log.Error("Failed to load directory entry data.", ex);
                                                throw;
                                            }
                                            finally
                                            {
                                                Transaction.Current = prevTransaction;
                                            }
                                        }
                                    )
                                );
                            }
                            else if (typeId.Key == DirectoryEntryTypes.Role)
                            {
                                dataLoadingTasks.Add
                                (
                                    Task.Factory.StartNew
                                    (
                                        () =>
                                        {
                                            var prevTransaction = Transaction.Current;
                                            Transaction.Current = transaction;

                                            try
                                            {
                                                using (var roleUsersCommand = new SqlCommandBuilder(connection))
                                                {
                                                    roleUsersCommand
                                                        .SetText("dbo.GetRolesUsers")
                                                        .AddParameter("@Ids", ids);

                                                    roleUsersCommand
                                                        .ExecuteReaderWithAction(rdr => ReadApplyRoleUser(children, rdr));
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                Log.Error("Failed to load users of roles.", ex);
                                                throw;
                                            }
                                            finally
                                            {
                                                Transaction.Current = prevTransaction;
                                            }
                                        }
                                    )
                                );
                            }
                            else if (typeId.Key == DirectoryEntryTypes.Workflow)
                            {
                                dataLoadingTasks.Add
                                (
                                    Task.Factory.StartNew
                                    (
                                        () =>
                                        {
                                            var prevTransaction = Transaction.Current;
                                            Transaction.Current = transaction;

                                            try
                                            {
                                                using (var wfPreferredProcsCommand = new SqlCommandBuilder(connection))
                                                {
                                                    wfPreferredProcsCommand
                                                        .SetText("dbo.GetWorkflowsPreferredProcessors")
                                                        .AddParameter("@Ids", ids);

                                                    wfPreferredProcsCommand
                                                        .ExecuteReaderWithAction(rdr => ReadApplyWorkflowPreferredProcessor(children, rdr));
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                Log.Error("Failed to load preferred processors of workflows.", ex);
                                                throw;
                                            }
                                            finally
                                            {
                                                Transaction.Current = prevTransaction;
                                            }
                                        }
                                    )
                                );
                            }
                        }
                    }
                    else
                    {
                        if (entryTypes == DirectoryEntryTypes.Role)
                        {
                            dataLoadingTasks.Add
                            (
                                Task.Factory.StartNew
                                (
                                    () =>
                                    {
                                        var prevTransaction = Transaction.Current;
                                        Transaction.Current = transaction;

                                        try
                                        {
                                            using (var roleUsersCommand = new SqlCommandBuilder(connection))
                                            {
                                                roleUsersCommand
                                                    .SetText("dbo.GetRolesUsers")
                                                    .AddParameter("@Ids", children.Select(c => Util.IdToLong(c.Id)));

                                                roleUsersCommand
                                                    .ExecuteReaderWithAction(rdr => ReadApplyRoleUser(children, rdr));
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            Log.Error("Failed to load users of roles.", ex);
                                            throw;
                                        }
                                        finally
                                        {
                                            Transaction.Current = prevTransaction;
                                        }
                                    }
                                )
                            );
                        }
                        else if (entryTypes == DirectoryEntryTypes.Workflow)
                        {
                            dataLoadingTasks.Add
                            (
                                Task.Factory.StartNew
                                (
                                    () =>
                                    {
                                        var prevTransaction = Transaction.Current;
                                        Transaction.Current = transaction;

                                        try
                                        {
                                            using (var wfPreferredProcsCommand = new SqlCommandBuilder(connection))
                                            {
                                                wfPreferredProcsCommand
                                                    .SetText("dbo.GetWorkflowsPreferredProcessors")
                                                    .AddParameter("@Ids", children.Select(c => Util.IdToLong(c.Id)));

                                                wfPreferredProcsCommand
                                                    .ExecuteReaderWithAction(rdr => ReadApplyWorkflowPreferredProcessor(children, rdr));
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            Log.Error("Failed to load preferred processors of workflows.", ex);
                                            throw;
                                        }
                                        finally
                                        {
                                            Transaction.Current = prevTransaction;
                                        }
                                    }
                                )
                            );
                        }
                    }
                }

                Exception aclLoadingException = null;

                try
                {
                    using (var aclCommand = new SqlCommandBuilder(connection))
                    {
                        aclCommand
                            .SetText("dbo.GetAccessControlLists")
                            .AddParameter("@Ids", children.Select(ent => Util.IdToLong(ent.Id)))
                            .ExecuteReaderWithAction(rdr => ReadApplyAcl(children, rdr));
                    }
                }
                catch(Exception ex)
                {
                    aclLoadingException = ex;
                    Log.Error("ACL loading failed", ex);
                }

                Task.WaitAll(dataLoadingTasks.ToArray());

                if (aclLoadingException != null)
                {
                    throw new FaultException<DirectoryFault>
                    (
                        new DirectoryFault
                        {
                            Code = DirectoryFaultCode.ServerError
                        }
                    );
                }

                return children;
            }
        }

        public IList<DirectoryEntryInfo> GetChildrenInfo(string parent, DirectoryEntryTypes entryTypes, SortProperty property, SortOrder order, long offset, long limit)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                var children = new List<DirectoryEntryInfo>();

                bool limitFilter = limit > offset;
                bool offsetFilter = offset > 0 || limitFilter;

                string procName =
                    string.Concat
                    (
                        "dbo.GetChildrenInfo",
                        (offsetFilter) ? "Offset" : string.Empty,
                        (limitFilter) ? "Limit" : string.Empty,
                        property.ToString(),
                        order.ToString()
                    );

                using (var getDirectoryEntriesCommand = new SqlCommandBuilder(connection))
                {
                    getDirectoryEntriesCommand
                        .SetText(procName)
                        .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                        .AddParameter("@Parent", parent)
                        .AddParameter("@Type", (int)entryTypes);

                    if (offsetFilter)
                    {
                        getDirectoryEntriesCommand
                            .AddParameter("@Offset", offset);
                    }

                    if (limitFilter)
                    {
                        getDirectoryEntriesCommand
                            .AddParameter("@Limit", limit);
                    }

                    try
                    {
                        children.AddRange
                        (
                            getDirectoryEntriesCommand.ExecuteReader(ReadDirectoryEntryInfo)
                        );
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result > 1)
                        {
                            throw;
                        }
                    }

                    if (children.Count == 0)
                    {
                        return children;
                    }
                }

                using (var aclCommand = new SqlCommandBuilder(connection))
                {
                    aclCommand
                        .SetText("dbo.GetAccessControlLists")
                        .AddParameter("@Ids", children.Select(ent => Util.IdToLong(ent.Id)))
                        .ExecuteReaderWithAction(rdr => ReadApplyAcl(children, rdr));
                }

                return children;
            }
        }

        public long CountChildren(string parent, DirectoryEntryTypes entryTypes)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var command = new SqlCommandBuilder(connection))
                {
                    return
                        command
                            .SetText("dbo.CountChildren")
                            .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                            .AddParameter("@Parent", parent)
                            .AddParameter("@Type", (int)entryTypes)
                            .ExecuteLong();
                }
            }
        }

        public DirectoryEntry GetAncestor(string child, int n, DirectoryEntryTypes ancestorEntryTypes)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var getAncestorCommand = new SqlCommandBuilder(connection))
                {
                    DirectoryEntry result;

                    try
                    {
                        result =
                            getAncestorCommand
                                .SetText("dbo.GetAncestor")
                                .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                                .AddParameter("@PathOrId", child)
                                .AddParameter("@N", n)
                                .AddParameter("@TypeFilter", (int)ancestorEntryTypes)
                                .ExecuteSingleRow(rdr => ReadDirectoryEntry(new SpecificFieldsReader(), rdr));
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result == 1)
                        {
                            return null;
                        }

                        throw;
                    }

                    if (result == null)
                    {
                        return null;
                    }

                    LoadAcl(connection, result);

                    return result;
                }
            }
        }

        public DirectoryEntryInfo GetAncestorInfo(string child, int n, DirectoryEntryTypes ancestorEntryTypes)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var getAncestorCommand = new SqlCommandBuilder(connection))
                {
                    DirectoryEntryInfo result;

                    try
                    {
                        result =
                            getAncestorCommand
                                .SetText("dbo.GetAncestorInfo")
                                .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                                .AddParameter("@PathOrId", child)
                                .AddParameter("@N", n)
                                .AddParameter("@TypeFilter", (int)ancestorEntryTypes)
                                .ExecuteSingleRow(ReadDirectoryEntryInfo);
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result == 1)
                        {
                            return null;
                        }

                        throw;
                    }
                    
                    if (result == null)
                    {
                        return null;
                    }

                    LoadAcl(connection, result);

                    return result;
                }
            }
        }

        public string Save(string parent, DirectoryEntry entry)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                
                long id;

                using (var saveCommand = new SqlCommandBuilder(connection))
                {
                    saveCommand
                        .SetText("dbo.Save" + entry.GetType().Name)
                        .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                        .AddParameter("@Parent", parent);

                    AddCommonParameters(saveCommand, entry);

                    entry.AcceptVisitor(new SpecificFieldsSaver { Command = saveCommand });
                    
                    try
                    {
                        id = saveCommand.ExecuteLongOutParamProcedure("@NewId");
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        switch (ex.Result)
                        {
                            case 1:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.EntryNotFound
                                    },
                                    string.Format("Parent entry {0} doesn't exist.", parent)
                                );
                            case 2:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.EntryNotFound
                                    },
                                    string.Format("Entry {0} doesn't exist.", entry.Id)
                                );
                            case 3:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.AccessDenied
                                    },
                                    "You have no permission to perform the operation."
                                );
                            case 4:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.NameCollision
                                    },
                                    string.Format("Parent entry {0} already contains an entry with the name {1}.", parent, entry.Name)
                                );
                            default:
                                throw;
                        }
                    }
                }

                using (var saveAclCommand = new SqlCommandBuilder(connection))
                {
                    saveAclCommand
                        .SetText("dbo.SaveAccessControlList")
                        .AddParameter("@DirectoryEntryId", id)
                        .AddAclListParameter("@AccessControlList", entry.AccessControlList)
                        .Execute();
                }

                if (entry is Role)
                {
                    _authorizationProvider.RefreshRoles();
                }

                return "#" + id;
            }
        }

        public void Move(string what, string where)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var moveCommand = new SqlCommandBuilder(connection))
                {
                    try
                    {
                        moveCommand
                            .SetText("dbo.MoveDirectoryEntry")
                            .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                            .AddParameter("@What", what)
                            .AddParameter("@Where", where)
                            .Execute();
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        switch (ex.Result)
                        {
                            case 1:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.EntryNotFound
                                    },
                                    "Entry with the specified id or path doesn't exist."
                                );
                            case 2:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.AccessDenied
                                    },
                                    "You have no permission to perform the operation."
                                );
                            case 3:
                                throw new FaultException<DirectoryFault>
                                (
                                    new DirectoryFault
                                    {
                                        Code = DirectoryFaultCode.NameCollision
                                    },
                                    "Parent entry already contains an entry with the same name."
                                );
                            default:
                                throw;
                        }
                    }
                }
            }

            if (what.StartsWith("/Roles") || where.StartsWith("/Roles"))
            {
                _authorizationProvider.RefreshRoles();
            }
        }

        public void Remove(string what)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var removeCommand = new SqlCommandBuilder(connection))
                {
                    try
                    {
                        removeCommand
                            .SetText("dbo.RemoveDirectoryEntry")
                            .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                            .AddParameter("@PathOrId", what)
                            .Execute();
                    }
                    catch (SqlCommandErrorResultException ex)
                    {
                        if (ex.Result == 2)
                        {
                            throw new FaultException<DirectoryFault>
                            (
                                new DirectoryFault
                                {
                                    Code = DirectoryFaultCode.AccessDenied
                                },
                                "You have no permission to perform the operation."
                            );
                        } 
                        
                        if (ex.Result > 1)
                        {
                            throw;
                        }
                    }
                }
            }

            if (what.StartsWith("/Roles"))
            {
                _authorizationProvider.RefreshRoles();
            }
        }

        public bool Exists(string what, DateTime timestamp)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var existsCommand = new SqlCommandBuilder(connection))
                {
                    return 
                        existsCommand
                            .SetText("dbo.DirectoryEntryExists")
                            .AddParameter("@UserRoles", _authorizationProvider.GetUserRoles(GetCurrentUser()))
                            .AddParameter("@PathOrId", what)
                            .AddParameter("@Timestamp", timestamp)
                            .ExecuteInt() != 0;
                }
            }
        }

        public ICollection<ScriptParameter> ExecuteScript(string script, ICollection<ScriptParameter> args)
        {
            return _scriptRunner.ExecuteScript(GetCurrentUser().Name, script, args);
        }

        #endregion
    }
}
